package com.honeycomb.db.dao.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.honeycomb.article.DataRef;
import com.honeycomb.article.PositionScope;
import com.honeycomb.article.SearchPattern;
import com.honeycomb.article.dto.ArticleCatagory;
import com.honeycomb.article.dto.ArticleDetailMessageDto;
import com.honeycomb.article.dto.ArticleinfoDto;
import com.honeycomb.article.dto.Author;
import com.honeycomb.article.dto.ContentStatus;
import com.honeycomb.article.dto.MessageType;
import com.honeycomb.common.PositionInfo;
import com.honeycomb.common.base.dao.impl.BaseDaoImpl;
import com.honeycomb.db.dao.ArticleinfoDao;
import com.honeycomb.user.dto.UserinfoDto.Sex;
import com.honeycomb.utils.MyDateDTTM;

/**
 * 文章dao实现
 * 
 * @author Wangyu
 */
@Repository
public class ArticleinfoDaoImpl extends BaseDaoImpl implements ArticleinfoDao {
	
	private JdbcTemplate jdbcTemplate;
	
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	@Override
	public List<ArticleinfoDto> searchArticles(ArticleCatagory articleCatagory,
													PositionScope positionScope,
													SearchPattern searchPattern, 
													DataRef firstDataRef,
													int countLimit) {
		
		StringBuffer selectSql = new StringBuffer("SELECT article_id, u.user_id user_id, user_logo_url, user_nickname, user_sex, user_statement, article_create_ip, art_position_addr, art_position_lat, art_position_lng, art_position_providerId, article_status, article_create_dttm, art_summary_pic_url, art_summary_text, article_title, article_type");
		StringBuffer fromSql = new StringBuffer(" FROM article_info a, user_info u");
		StringBuffer commonWhereSql = new StringBuffer(" WHERE ").append("article_status > ").append(ContentStatus.BELOW_IS_HIDDEN.toIntValue()).append(" AND art_position_lng > ").append(positionScope.getMinX()).append(" AND art_position_lng < ").append(positionScope.getMaxX()).append(" AND art_position_lat > ").append(positionScope.getMinY()).append(" AND art_position_lat < ").append(positionScope.getMaxY());
		StringBuffer extraWhereSql = new StringBuffer();
		if (!articleCatagory.equals(ArticleCatagory.ALL)) {
			extraWhereSql.append(" AND article_type=").append(articleCatagory.toIntValue()); 
		}
		StringBuffer tableUnionSql = new StringBuffer(" AND a.user_id=u.user_id");
		StringBuffer orderbySql = new StringBuffer(" ORDER BY ");
		StringBuffer limitSql = new StringBuffer(" LIMIT ");

		
		switch(searchPattern) {
			case LAST_FIRST :
				if (firstDataRef.getDataID() != null) {
					extraWhereSql.append(" AND article_id <= ").append(firstDataRef.getDataID().longValue());
					limitSql.append(countLimit);
				}
				else {
					limitSql.append(firstDataRef.getDataIndex()).append(", ").append(countLimit);
				}
				orderbySql.append("article_id DESC");
				break;
		
			default : 
					logger.warn("SearchArticles does not support '" + searchPattern + "' Search Pattern.");
					orderbySql.append("article_id");
					limitSql.append(firstDataRef.getDataIndex()).append(", ").append(countLimit);
		}
		
		
		String sql = selectSql.append(fromSql).append(commonWhereSql).append(extraWhereSql).append(tableUnionSql).append(orderbySql).append(limitSql).toString();
		if (logger.isDebugEnabled()) {
			logger.debug("SearchArticles by sql: " + sql);
		}
		
		List<Map<String,Object>> result = jdbcTemplate.queryForList(sql);
		List<ArticleinfoDto> articleList = new ArrayList<ArticleinfoDto>(result.size());
		ArticleinfoDto article = null;
		for (Map<String,Object> elem : result){
			article = new ArticleinfoDto((Long) elem.get("article_id"));
			article.setAuthor(new Author((Long) elem.get("user_id")));
			article.getAuthor().setLogoUrl((String) elem.get("user_logo_url"));
			article.getAuthor().setName((String) elem.get("user_nickname"));
			article.getAuthor().setSex(Sex.getSex((Integer) elem.get("user_sex")));
			article.getAuthor().setIp((String) elem.get("article_create_ip"));
			article.getAuthor().setStatement((String) elem.get("user_statement"));
			
			article.setPositionInfo(new PositionInfo());
			article.getPositionInfo().setAddress((String) elem.get("art_position_addr"));
			article.getPositionInfo().setLatitude((Double) elem.get("art_position_lat"));
			article.getPositionInfo().setLongitude((Double) elem.get("art_position_lng"));
			article.getPositionInfo().setMapProviderId((Integer) elem.get("art_position_providerId"));
			
			article.setStatus(ContentStatus.getContentStatus( (Integer) elem.get("article_status") ) );
			article.setSubmitDate(new MyDateDTTM( (Integer) elem.get("article_create_dttm") ) );
			article.setSummaryPictureUrl((String) elem.get("art_summary_pic_url"));
			article.setTextSummary((String) elem.get("art_summary_text"));
			article.setTitle((String) elem.get("article_title"));
			article.setCatagory(ArticleCatagory.getArticleCatagory( (Integer) elem.get("article_type") ) );

			articleList.add(article);
		}
		
		if (logger.isDebugEnabled()) {
			logger.debug("SearchArticles, " + articleList.size() + " items are found.");
		}
		
		return articleList;
	}

	@Override
	public List<ArticleDetailMessageDto> getDetailMessagesOfArticle(Long articleId,
																SearchPattern searchPattern,
																DataRef firstDataRef,
																int countLimit) {
		StringBuffer selectSql = new StringBuffer("SELECT msg_id, article_id, u.user_id user_id, user_sex, msg_create_ip, user_logo_url, user_nickname, user_statement, msg_content, msg_position_addr, msg_position_lat, msg_position_lng, msg_position_providerId, msg_status, msg_create_dttm, msg_summary_pic_url, msg_summary_text, msg_title, msg_type");
		StringBuffer fromSql = new StringBuffer(" FROM article_detail_msg a, user_info u");
		StringBuffer commonWhereSql = new StringBuffer(" WHERE article_id=").append(articleId).append(" AND msg_status > ").append(ContentStatus.BELOW_IS_HIDDEN.toIntValue());
		StringBuffer tableUnionSql = new StringBuffer(" AND a.user_id=u.user_id");
		StringBuffer extraWhereSql = new StringBuffer();
		StringBuffer orderbySql = new StringBuffer(" ORDER BY ");
		StringBuffer limitSql = new StringBuffer(" LIMIT ");

		
		switch(searchPattern) {
			case EARLY_FIRST :
				if (firstDataRef.getDataID() != null) {
					extraWhereSql.append(" AND msg_id >= ").append(firstDataRef.getDataID().longValue());
					limitSql.append(countLimit);
				}
				else {
					limitSql.append(firstDataRef.getDataIndex()).append(", ").append(countLimit);
				}
				orderbySql.append("msg_id");
				break;
				
			case LAST_FIRST :
				if (firstDataRef.getDataID() != null) {
					extraWhereSql.append(" AND msg_id <= ").append(firstDataRef.getDataID().longValue());
					limitSql.append(countLimit);
				}
				else {
					limitSql.append(firstDataRef.getDataIndex()).append(", ").append(countLimit);
				}
				orderbySql.append("msg_id DESC");
				break;
		
			default :
					logger.warn("SearchArticles does not support '" + searchPattern + "' Search Pattern.");
					orderbySql.append("msg_id");
					limitSql.append(firstDataRef.getDataIndex()).append(", ").append(countLimit);
		}
		
		String sql = selectSql.append(fromSql).append(commonWhereSql).append(extraWhereSql).append(tableUnionSql).append(orderbySql).append(limitSql).toString();
		if (logger.isDebugEnabled()) {
			logger.debug("getDetailMessagesOfArticle by sql: " + sql);
		}
		
		List<Map<String,Object>> result = jdbcTemplate.queryForList(sql);
		
		List<ArticleDetailMessageDto> articleDetailList = new ArrayList<ArticleDetailMessageDto>(result.size());
		ArticleDetailMessageDto articleDetail = null;
		for (Map<String,Object> elem : result){
			articleDetail = new ArticleDetailMessageDto((Long) elem.get("msg_id") );
			articleDetail.setArticleId((Long) elem.get("article_id"));
			articleDetail.setAuthor(new Author((Long) elem.get("user_id") ) );
			articleDetail.getAuthor().setSex(Sex.getSex((Integer) elem.get("user_sex") ) );
			articleDetail.getAuthor().setIp((String) elem.get("msg_create_ip") );
			articleDetail.getAuthor().setLogoUrl((String) elem.get("user_logo_url") );
			articleDetail.getAuthor().setName((String) elem.get("user_nickname") );
			articleDetail.getAuthor().setStatement((String) elem.get("user_statement"));
			
			articleDetail.setContent((String) elem.get("msg_content") );

			articleDetail.setPositionInfo(new PositionInfo() );
			articleDetail.getPositionInfo().setAddress((String) elem.get("msg_position_addr") );
			articleDetail.getPositionInfo().setLatitude((Double) elem.get("msg_position_lat")  );
			articleDetail.getPositionInfo().setLongitude((Double) elem.get("msg_position_lng") );
			articleDetail.getPositionInfo().setMapProviderId( (Integer) elem.get("msg_position_providerId") );
			
			articleDetail.setStatus(ContentStatus.getContentStatus((Integer) elem.get("msg_status") ) );
			articleDetail.setSubmitDate(new MyDateDTTM( (Integer) elem.get("msg_create_dttm") ) );
			articleDetail.setSummaryPictureUrl((String) elem.get("msg_summary_pic_url") );
			articleDetail.setTextSummary((String) elem.get("msg_summary_text") );
			articleDetail.setTitle((String) elem.get("msg_title") );
			articleDetail.setType(MessageType.getMessageType( (Integer) elem.get("msg_type") ) );
			
			articleDetailList.add(articleDetail);
		}
		
		if (logger.isDebugEnabled()) {
			logger.debug("getDetailMessagesOfArticle, " + articleDetailList.size() + " items are found.");
		}
		
		return articleDetailList;
	}

	@Override
	public void doSaveReply(ArticleDetailMessageDto detailMessage) {
		String sql = "insert into article_detail_msg(msg_title, msg_summary_text, msg_summary_pic_url, msg_content, msg_create_dttm, msg_create_ip, msg_status, msg_type, msg_position_lng, msg_position_lat, msg_position_addr, msg_position_providerId, user_id, article_id) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[] val = {detailMessage.getTitle(),
							detailMessage.getTextSummary(),
							detailMessage.getSummaryPictureUrl(),
							detailMessage.getContent(),
							detailMessage.getSubmitDate().value(),
							detailMessage.getAuthor().getIp(),
							detailMessage.getStatus().toIntValue(),
							detailMessage.getType().toIntValue(),
							detailMessage.getPositionInfo().getLongitude(),
							detailMessage.getPositionInfo().getLatitude(),
							detailMessage.getPositionInfo().getAddress(),
							detailMessage.getPositionInfo().getMapProviderId(),
							detailMessage.getAuthor().getId(),
							detailMessage.getArticleId()
						};
		jdbcTemplate.update(sql, val);
	}
	
	@Override
	public void doSaveArticle(ArticleinfoDto articleInfo, ArticleDetailMessageDto detailMessage) {
		String sql = "insert into article_info(article_title, art_summary_text, art_summary_pic_url, article_create_dttm, article_create_ip, article_status, article_type, art_position_lng, art_position_lat, art_position_addr, art_position_providerId, user_id) values(?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[] val = {articleInfo.getTitle(),
						articleInfo.getTextSummary(),
						articleInfo.getSummaryPictureUrl(),
						articleInfo.getSubmitDate().value(),
						articleInfo.getAuthor().getIp(),
						articleInfo.getStatus().toIntValue(),
						articleInfo.getCatagory().toIntValue(),
						articleInfo.getPositionInfo().getLongitude(),
						articleInfo.getPositionInfo().getLatitude(),
						articleInfo.getPositionInfo().getAddress(),
						articleInfo.getPositionInfo().getMapProviderId(),
						articleInfo.getAuthor().getId()
					};
		jdbcTemplate.update(sql, val);
		
		//get the id of insert.
		Long articleID = jdbcTemplate.queryForLong("select LAST_INSERT_ID()");
		if (articleID == 0) {
			articleID = jdbcTemplate.queryForLong("SELECT article_id FROM article_info WHERE article_title = ? AND article_create_dttm=? AND user_id=?",
														new Object[]{articleInfo.getTitle(), articleInfo.getSubmitDate().value(), articleInfo.getAuthor().getId()}
													);
		}
		detailMessage.setArticleId(articleID);
		
		String sql2 = "insert into article_detail_msg(msg_title, msg_summary_text, msg_summary_pic_url, msg_content, msg_create_dttm, msg_create_ip, msg_status, msg_type, msg_position_lng, msg_position_lat, msg_position_addr, msg_position_providerId, user_id, article_id) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[] val2 = {detailMessage.getTitle(),
							detailMessage.getTextSummary(),
							detailMessage.getSummaryPictureUrl(),
							detailMessage.getContent(),
							detailMessage.getSubmitDate().value(),
							detailMessage.getAuthor().getIp(),
							detailMessage.getStatus().toIntValue(),
							detailMessage.getType().toIntValue(),
							detailMessage.getPositionInfo().getLongitude(),
							detailMessage.getPositionInfo().getLatitude(),
							detailMessage.getPositionInfo().getAddress(),
							detailMessage.getPositionInfo().getMapProviderId(),
							detailMessage.getAuthor().getId(),
							detailMessage.getArticleId()
						};
		jdbcTemplate.update(sql2, val2);
	}

	@Override
	public void doChangeMessage(Long messageId, String summary, String summaryPicUrl, String content,String title, MyDateDTTM updateDateDTTM, String ip,Long userId) {
		// check whether the user is the author himself, otherwise update is not allow.
		String sql0 = "SELECT msg_type, user_id, article_id FROM article_detail_msg WHERE msg_id=?";
		Map<String, Object> elem = jdbcTemplate.queryForMap(sql0, messageId);
		Long authorId = (Long)elem.get("user_id");
		Long articleId = (Long)elem.get("article_id");
		MessageType msgType = MessageType.getMessageType((Integer)elem.get("msg_type")); 
		
		if (authorId != userId.longValue() ) {
			throw new IllegalArgumentException("You do not have permission to change this message.");
		}
		
		String sql1 = "UPDATE article_detail_msg set msg_title=?, msg_summary_text=?, msg_summary_pic_url=?, msg_content=?, msg_create_dttm=?, msg_create_ip=? WHERE msg_id=?";
		Object[] val1 = {title, summary, summaryPicUrl,content, updateDateDTTM.value(), ip, messageId};
		jdbcTemplate.update(sql1, val1);
		
		// if the message is main message of an article, then the article information should be changed as well.
		if (msgType.equals(MessageType.MAIN)) {
			String sql2 = "UPDATE article_info set article_title=?, art_summary_text=?, art_summary_pic_url=?, article_create_dttm=?, article_create_ip=? WHERE article_id=?";
			Object[] val2 = {title, summary, summaryPicUrl, updateDateDTTM.value(), ip, articleId};
			jdbcTemplate.update(sql2, val2);
		}
	}
	
	@Override
	public ArticleDetailMessageDto getDetailMessage(Long messageId) {
		String sql = "SELECT msg_id, article_id, u.user_id user_id, user_sex, msg_create_ip, user_logo_url, user_nickname, user_statement, msg_content, msg_position_addr, msg_position_lat, msg_position_lng, msg_position_providerId, msg_status, msg_create_dttm, msg_summary_pic_url, msg_summary_text, msg_title, msg_type FROM article_detail_msg a, user_info u WHERE msg_id=? AND a.user_id=u.user_id";
		Map<String, Object> elem = jdbcTemplate.queryForMap(sql, messageId);
		
		ArticleDetailMessageDto message = new ArticleDetailMessageDto((Long) elem.get("msg_id") );
		message.setArticleId((Long) elem.get("article_id"));
		message.setAuthor(new Author((Long) elem.get("user_id") ) );
		message.getAuthor().setSex(Sex.getSex((Integer) elem.get("user_sex") ) );
		message.getAuthor().setIp((String) elem.get("msg_create_ip") );
		message.getAuthor().setLogoUrl((String) elem.get("user_logo_url") );
		message.getAuthor().setName((String) elem.get("user_nickname") );
		message.getAuthor().setStatement((String) elem.get("user_statement") );
		
		
		message.setContent((String) elem.get("msg_content") );

		message.setPositionInfo(new PositionInfo() );
		message.getPositionInfo().setAddress((String) elem.get("msg_position_addr") );
		message.getPositionInfo().setLatitude((Double) elem.get("msg_position_lat")  );
		message.getPositionInfo().setLongitude((Double) elem.get("msg_position_lng") );
		message.getPositionInfo().setMapProviderId( (Integer) elem.get("msg_position_providerId") );
		
		message.setStatus(ContentStatus.getContentStatus((Integer) elem.get("msg_status") ) );
		message.setSubmitDate(new MyDateDTTM( (Integer) elem.get("msg_create_dttm") ) );
		message.setSummaryPictureUrl((String) elem.get("msg_summary_pic_url") );
		message.setTextSummary((String) elem.get("msg_summary_text") );
		message.setTitle((String) elem.get("msg_title") );
		message.setType(MessageType.getMessageType( (Integer) elem.get("msg_type") ) );
		
		return message;
	}

	/**
	 * If the main article is deleted, return true; if only message deleted, return false;
	 */
	@Override
	public boolean doDeleteMessage(Long messageId, Long userId) {
		String sql0 = "SELECT msg_type, user_id, article_id FROM article_detail_msg WHERE msg_id=?";
		Map<String, Object> elem = jdbcTemplate.queryForMap(sql0, messageId);
		Long authorId = (Long)elem.get("user_id");
		Long articleId = (Long)elem.get("article_id");
		MessageType msgType = MessageType.getMessageType((Integer)elem.get("msg_type")); 
		
		if (authorId != userId.longValue() ) {
			throw new IllegalArgumentException("You do not have permission to change this message.");
		}
		
		String sql1 = "UPDATE article_detail_msg set msg_status=? WHERE msg_id=?";
		jdbcTemplate.update(sql1, new Object[] {ContentStatus.DELETED.toIntValue(), messageId});
		
		// if the message is main message of an article, then the article information should be changed as well.
		if (msgType.equals(MessageType.MAIN)) {
			String sql2 = "UPDATE article_info set article_status=? WHERE article_id=?";
			jdbcTemplate.update(sql2, new Object[] {ContentStatus.DELETED.toIntValue(), articleId} );
			
			return true;
		}
		
		return false;
	}
	
}
